#!/usr/bin/env python

import sys, re, time, os
import types
import xmlrpclib
import config
import datetime
#from pysqlite2 import dbapi2 as sqlite
import sqlite3 as sqlite

class db:
	def __init__(self, dbfile="foodstorage.db"):
		"""
		>>> d = db()
		"""
		self.dbfile = dbfile
		self.conn = sqlite.connect(dbfile)

	def __del__(self):
		self.conn.close()

	def get_cursor(self):
		return self.conn.cursor()

	def expiring_items(self, num_months):
		"""
		>>> d = db()
		>>> r = d.expiring_items(1)
		"""
		cur = self.get_cursor()
		cur2 = self.get_cursor()
		cur2.execute("create temp table expiring_items (description, size, expire_date);")
		cur.execute("select ut.desc, u.purchase_date, t.shelf_life, ut.amt, ut.unit as size  from upcs u, upc_types ut, types t where u.upc = ut.upc and ut.type_id = t.id;")
		row = cur.fetchone()
		while row:
			desc, pd, sl, amt, unit = row
			size = "%s %s" % (amt, unit)
			expire_date = self.add_months(sl, pd)
			if self.will_expire(num_months, expire_date):
				cur2.execute("insert into expiring_items (description, size, expire_date) values ('%s', '%s', '%s');" % (desc, size, expire_date))
			row = cur.fetchone()
		self.conn.commit()
		cur.close()
		cur2.execute("select description, size, expire_date, count(*) from expiring_items group by description, size, expire_date order by expire_date, description, size;")
		expires = cur2.fetchall()
		cur2.close()
		return expires

	def get_checked_out_list(self, days_ago):
		"""
		returns a relation of 3 tuples
		"""
		cur = self.get_cursor()
		today = datetime.date.today()
		delta = datetime.timedelta(days=int(days_ago))
		old_date = today - delta
		cur.execute("select t.type, ut.desc, count(*) from types t, upc_types ut, deleted_upcs du where t.id = ut.type_id and ut.upc = du.upc and du.deleted_date >= '%s' group by t.type,  ut.desc order by t.type, ut.desc;" % old_date.strftime("%Y-%m-%d"))
		rows = cur.fetchall()
		cur.close()
		return rows

	def add_months(self, shelf_life, purchase_date):
		"""
		Returns the expire date given the parameters

		>>> d = db()
		>>> d.add_months(12, '2005-01')
		'2006-01'
		"""
		po = os.popen(""" date -d'%s-01 +%s month' +%%Y-%%m """ % (purchase_date, shelf_life), "r")
		new_date = po.read().replace("\n", "")
		po.close()
		return new_date

	def will_expire(self, num_months, expire_date):
		"""
		Returns true if expire_date is num_months or less
		months from the current month

		>>> d = db()
		>>> import time
		>>> d.will_expire(0, time.strftime("%Y-%m"))
		1
		"""
		po = os.popen(""" date -d'+%s month' +%%Y-%%m """ % num_months, "r")
		adj_month = po.read().replace("\n", "")
		po.close()
		if str(adj_month) >= str(expire_date):
			return 1
		else:
			return 0

	def upc_type_there(self, upc):
		"""
		>>> d = db()
		>>> d.upc_type_there("019722510704")
		1
		>>> d.upc_type_there("butter")
		0
		"""
		cur = self.get_cursor()
		cur.execute("select upc from upc_types where upc = '%s' limit 1;" % upc)
		row = cur.fetchone()
		cur.close()
		if row:
			return 1
		else:
			return 0

	def get_type_info_from_id(self, id):
		"""
		>>> d = db()
		>>> d.get_type_info_from_id(100029292)
		['', '', '', '']
		"""
		cur = self.get_cursor()
		cur.execute("select type, shelf_life, goal_amt, goal_unit from types where id = '%s';" % id)
		row = cur.fetchone()
		cur.close()
		if row:
			return row
		else:
			return ["", "", "", ""]

	def get_type_list_detail(self):
		"""
		>>> d = db()
		>>> len(d.get_type_list_detail()[0]) == 5
		1
		"""
		cur = self.get_cursor()
		sql = "select id, type, shelf_life, goal_amt, goal_unit from types order by type;"
		cur.execute(sql)
		rows = cur.fetchall()
		cur.close()
		return rows

	def get_type_list(self):
		"""
		>>> d = db()
		>>> len(d.get_type_list()) > 5
		1
		"""
		cur = self.get_cursor()
		cur.execute("select id, type from types order by type;")
		rows = cur.fetchall()
		cur.close()
		return rows

	def add_type(self, type, shelf_life, goal, goal_unit):
		"""
		>>> d = db()
		"""
		cur = self.get_cursor()
		goal_unit = self.norm_unit(goal_unit)
		cur.execute("insert into types (type, shelf_life, goal_amt, goal_unit) values ('%s', '%s', '%s', '%s');" % (type, shelf_life, goal, goal_unit))
		self.conn.commit()
		cur.close()

	def update_type(self, id, type, shelf_life, goal, goal_unit):
		"""
		>>> d = db()
		"""
		cur = self.get_cursor()
		goal_unit = self.norm_unit(goal_unit)
		cur.execute("update types set type='%s', shelf_life='%s', goal_amt='%s', goal_unit='%s' where id = '%s';" % (type, shelf_life, goal, goal_unit, id))
		self.conn.commit()
		cur.close()

	def add_upc_type(self, upc, type_id, amt, unit, desc):
		"""
		>>> d = db()
		"""
		if self.upc_type_there(upc):
			return
		cur = self.get_cursor()
		unit = self.norm_unit(unit)
		cur.execute("insert into upc_types (upc, type_id, amt, unit, desc) values ('%s', '%s', '%s', '%s', '%s');" % (upc, type_id, amt, unit, desc))
		self.conn.commit()
		cur.close()

	def get_percent_of_goal(self, category="all"):
		"""
		Returns a list of 2 tuples with strings of the form
		(category, percent_of_goal)

		>>> d = db()
		>>> import types
		>>> r = d.get_percent_of_goal()
		>>> type(r) == types.ListType
		True
		"""
		if category == "all":
			returnlist = []
			categories = self.get_all_categories()
			for cat in categories:
				percent, amt, goalunit = self.get_percent_of_goal_category(cat)
				returnlist.append((cat, percent, amt, goalunit))
			returnlist.sort(self._sort_percentages)
			return returnlist
		else:
			percent, amt, goalunit= self.get_percent_of_goal_category(category)
			returnlist.sort(self._sort_percentages)
			return (category, percent, amt, goalunit)

	def _sort_percentages(self, p1, p2):
		return cmp(float(p1[1]), float(p2[1]))

	def get_percent_of_goal_category(self, category):
		"""
		returns the percent of goal for category
		"""
		cur = self.get_cursor()
		# Gather all items in category
		cur.execute("select t.goal_amt, t.goal_unit, ut.amt, ut.unit from types t, upc_types ut, upcs u where t.type = '%s' and t.id = ut.type_id and ut.upc = u.upc;" % category)
		results = cur.fetchall()
		cur.close()
		totalamt = 0.0
		for item in results:
			goal_amt, goal_unit, amt, unit = item
			# normalize here
			amt = self.norm_amt(amt, unit, goal_unit)
			# add to totalamt here
			totalamt += float(amt)
		goal_amt = float(goal_amt) * config.familymembers
		percent = self.get_percent(goal_amt, totalamt)
		totalamt = round(totalamt, 1)
		return (percent, totalamt, goal_unit)

	def norm_amt(self, amt, unit, goalunit):
		"""
		>>> d = db()
		>>> r = d.norm_amt("64", "oz", "gal")
		>>> r == 0.5
		True
		"""
		if unit == goalunit:
			return float(amt)
		else:
			return getattr(self, "oz2%s" % goalunit)(amt)

	def oz2gal(self, amt):
		"""
		>>> d = db()
		>>> r = d.oz2gal("64")
		>>> r == 0.5
		True
		"""
		return float(amt)/128.0

	def oz2lb(self, oz):
		"""
		>>> d = db()
		>>> r = d.oz2lb("8")
		>>> r == 0.5
		True
		"""
		return float(oz)/16.0

	def norm_unit(self, unit):
		u = unit.strip().lower().replace(".", "")
		validunits = "lb oz gal items".split()
		if u not in validunits:
			raise Exception, "You entered %s as the unit.  Only %s are valid units." % (u, " ".join(validunits))
		return u

	def get_percent(self, goal_amt, totalamt):
		"""
		Returns the percent of goal_amt totalamt is
		as a string
		"""
		return str(round(100*(float(totalamt)/float(goal_amt)), 1))

	def get_all_categories(self):
		"""
		Returns a sequence of all categories

		>>> d = db()
		>>> r = d.get_all_categories()
		>>> import types
		>>> type(r) == types.ListType
		True
		"""
		cur = self.get_cursor()
		cur.execute("select distinct type from types order by type;")
		types = cur.fetchall()
		types = [item[0] for item in types]
		cur.close()
		return types

	def get_upc_detail(self, upc):
		"""
		Returns a list of upc details info
		"""
		cur = self.get_cursor()
		cur.execute("select ut.desc, ut.amt, ut.unit, u.purchase_date, t.type, t.shelf_life, t.goal_amt, t.goal_unit from types t, upc_types ut, upcs u where u.upc = '%s' and u.upc = ut.upc and ut.type_id = t.id;" % upc)
		detail = cur.fetchall()
		cur.close()
		return detail

	def get_upc_type(self, upc):
		"""
		Returns the upc type description given a upc
		"""
		if not self.upc_type_there(upc):
			return "No upc type"
		cur = self.get_cursor()
		cur.execute("select desc from upc_types where upc = '%s';" % upc)
		desc = cur.fetchone()
		cur.close()
		return desc[0]


	def add_upc(self, upc, date=""):
		if not date:
			date = time.strftime("%Y-%m")
		cur = self.get_cursor()
		cur.execute("insert into upcs (upc, purchase_date) values ('%s', '%s');" % (upc, date))
		self.conn.commit()
		cur.close()

	def del_upc(self, upc):
		"""
		Used to just delete from the upcs table, but now to allow the shopping list
		it will insert into the deleted_upcs table first before deleting.

		We are only dealing with one record at a time here.
		"""
		cur = self.get_cursor()
		cur.execute("select min(id) from upcs where upc = '%s';" % upc)
		id = cur.fetchone()
		id = id[0] # pysqlite always returns a tuple
		if not id:
			cur.close()
			return 0
		else:
			# create the record in deleted_upcs
			cur.execute("insert into deleted_upcs (id, upc, purchase_date, deleted_date) select id, upc, purchase_date, '%s' from upcs where id = %s;" %
				(time.strftime('%Y-%m-%d'), id))
			# now delete the record from the upcs table
			cur.execute("delete from upcs where id = '%s';" % id)
			self.conn.commit()
			cur.close()
			return 1

	def delete_type(self, typeid):
		cur = self.get_cursor()
		cur.execute("delete from types where id = '%s';" % typeid)
		rows = cur.close()
		self.conn.commit()
		return rows

import httplib, urllib

class upcdb:

	def __init__(self):
		self.site = "www.upcdatabase.com"

	def get_vars(self):
		self.desc = self.get_desc()
		self.amt  = self.get_amt()

	def get_desc(self):
		lines = self.data.split("\n")
		for line in lines:
			if re.search("Description", line):
				return line.replace("<tr><td>Description</td><td></td><td>", "").replace("</td></tr>", "")
		return ""

	def get_amt(self):
		lines = self.data.split("\n")
		for line in lines:
			if re.search("Size/Weight", line):
				return line.replace("<tr><td>Size/Weight</td><td></td><td>", "").replace("</td></tr>", "")
		return ""

	def known(self):
		lines = self.data.split("\n")
		for line in lines:
			if re.search("This UPC was not found in the database.", line):
				return 0
			if re.search("The UPC you entered is invalid or does not have a correct checksum", line):
				return 0
		return 1

	def upc_there(self, upc):
		"""
		>>> udb = upcdb()
		>>> udb.upc_there("019722510704")
		1
		>>> udb.amt
		'12.5 oz'
		>>> udb.desc
		'Kirkland Premium Chunk Chicken Breast'
		>>> udb.upc_there("test2")
		0
		"""
		s = xmlrpclib.Server("http://%s/rpc" % self.site)
		if len(upc) == 12: # UPC-A
			r = s.lookupUPC(upc)
		elif len(upc) == 13: # EAN/UCC-13
			r = s.lookupEAN(upc)
		elif len(upc) == 8: # UPC-E
			upc = s.convertUPCE(upc)
			r = s.lookupEAN(upc)
		else:
			return 0 # invalid upc
		if type(r) == types.DictType and "found" in r.keys() and r['found']:
			self.amt = r['size']
			self.desc = r['description']
			return 1
		else:
			return 0

	def add_upc(self, upc, amt, desc):
		"""
		>>> r = upcdb()
		>>> r.add_upc("018000009053", "18.25 oz", "Moist Supreme Classic Yellow Premium Cake Mix")
		1
		"""
		conn = httplib.HTTPConnection(self.site)
		params = urllib.urlencode({'upc': upc, 'description': desc, 'pkg_size': amt, 'submit': ' Save New Entry '})
		headers = {"Content-type": "application/x-www-form-urlencoded","Accept": "text/plain"}
		conn.request("POST", "/add.asp", params, headers)
		response = conn.getresponse()
		status = response.status
		reason = response.reason
		data   = response.read()
		conn.close()
		if status == 302 and reason == "Found":
			return 1
		else:
			print status, reason, data
			return 0

class reports:

	def __init__(self):
		self.db1 = db()

	def shopping_list(self):
		pass

	def per_goal_by_type(self):
		pass

	def items_detail_by_type(self):
		pass

	def item_total_count_by_type(self):
		pass


class runner:
	def _test(self):
		r = db()
		print r.get_percent_of_goal()

	def _doctest(self):
		import doctest, db
		doctest.testmod(db)

if __name__ == "__main__":
	r = runner()
	getattr(r, "_"+sys.argv[1])()
